﻿using System.Data.Common;
using System.Data.Objects;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Data.Entity;
using EFCTP5VSNH3.Model;
using System.Data.Entity.Infrastructure;

namespace EntityFrameworkDemo
{
    public class Db : DbContext
    {
        public Db()
        {
        }

        public DbSet<Developer> Developers
        {
            get;
            set;
        }

        public DbSet<Project> Projects
        {
            get;
            set;
        }

        public DbSet<Skill> Skills
        {
            get;
            set;
        }

        public DbSet<Customer> Customers
        {
            get;
            set;
        }

        public ObjectContext ObjContext
        {
            get
            {
                return (this as IObjectContextAdapter).ObjectContext;
            }
        }

        #region SQL and Stored Procedures

        public IEnumerable<Developer> NoTrackedGetDevelopersByName(string name)
        {
            return this.Database.SqlQuery<Developer>("EXECUTE LoadDevelopersByName @Name", new SqlParameter("Name", name));
        }

        public IEnumerable<Developer> TrackedGetDevelopersByName(string name)
        {
            return this.Developers.SqlQuery("EXECUTE LoadDevelopersByName @Name", new SqlParameter("Name", name));
        }

        public IEnumerable<Developer> GetDevelopersByProjectDescription1(string projectDescription)
        {
            return this.Database.SqlQuery<Developer>(
                string.Format("EXECUTE GetDevelopersByProjectDescription '{0}'", projectDescription),
                 new object[] { });
        }

        public IEnumerator<Developer> GetDevelopersByProjectDescription2(string projectDescription)
        {
            ObjectContext ctx = (this as IObjectContextAdapter).ObjectContext;

            ObjectResult<Developer> developers = ctx.ExecuteStoreQuery<Developer>(
                string.Format("EXECUTE GetDevelopersByProjectDescription '{0}'", projectDescription));

            return developers.GetEnumerator();
        }

        public IEnumerable<Developer> ExecuteDeveloperSqlQuery(string query)
        {
            return this.Developers.SqlQuery(query, new object[] { });
        }

        #endregion

        #region Model Configuration

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Customer>()
                   .Property(c => c.CompanyName)
                   .HasColumnName("NomeSocieta");

            modelBuilder.ComplexType<Adress>()
                .Property(a => a.City)
                .HasColumnName("Citta");

            modelBuilder.ComplexType<Adress>()
                .Property(a => a.StreetAddress)
                .HasColumnName("Indirizzo");

            modelBuilder.ComplexType<Adress>()
                .Property(a => a.Country)
                .HasColumnName("Nazione");

            modelBuilder
                .Entity<Developer>()
                .Property(d => d.Name)
                .HasColumnName("Nome");

            modelBuilder
                .Entity<Developer>()
                .Property(d => d.Surname)
                .HasColumnName("Cognome");

            modelBuilder
                .Entity<Skill>()
                .Property(s => s.Description)
                .HasColumnName("Descrizione");

            modelBuilder.Entity<Project>()
                .Property(p => p.Description)
                .HasColumnName("Descrizione");

            // Set many-to-many relationship.
            modelBuilder
                .Entity<Developer>()
                .HasMany<Project>(d => d.Projects)
                .WithMany(p => p.Developers)
                .Map(m =>
                {
                    // Association table.
                    m.ToTable("ProgettoSviluppatore");
                    // Map left Key.
                    m.MapLeftKey(new string[] { "SviluppatoreId" });
                    ////Map Right Key.
                    m.MapRightKey(new string[] { "ProgettoId" });
                });

            // Set relationship between developer and skills.
            modelBuilder
                .Entity<Developer>()
                .HasMany<Skill>(d => d.Skills)
                .WithOptional()
                .Map(m => m.MapKey(new string[] { "SviluppatoreId" }));

            modelBuilder
                .Entity<Architect>()
                .Property(a => a.Phone)
                .HasColumnName("Telefono");
        }

        #endregion
    }
}
